#ifndef __W_DB_H_
#define __W_DB_H_

#include "util.hpp"
#include <assert.h>
#include <mutex>

class user_table
{
public:
    //参数1:连接的mysql服务器的地址,参数2:连接的服务器的⽤⼾名，参数3:连接的服务器的密码，
    // 参数4:默认选择的数据库名称，参数5:连接的服务器的端⼝，默认0是3306端⼝
    user_table(const string& host, const string& username, const string& password,
                const string& dbname, uint16_t port = 3306)
    {
        _mysql = mysql_util::mysql_create(host, username, password, dbname, port);
        assert(_mysql != NULL);
    }
    ~user_table()
    {
        mysql_util::mysql_destroy(_mysql);
        _mysql = NULL;
    }
    bool insert(Json::Value& user) //注册时新增用户
    {
         //对用户名和密码进行校验，任意一个没有都视为错误
        if(user["username"].isNull() || user["password"].isNull()) 
        {
            DLOG("input username or password!");
            return false;
        }

    #define INSERT_USER "insert user values(null, '%s', password('%s'), 1000, 0, 0);"    
        char sql[4096] = {0};
        sprintf(sql, INSERT_USER, user["username"].asCString(), user["password"].asCString());
        bool ret = mysql_util::mysql_exec(_mysql, sql);
        if(ret == false)
        {
            DLOG("insert user info failed!");
            return false;
        }
        return true;
    }
    bool login(Json::Value& user)  //登录时验证，返回详细的用户信息
    {
        if(user["username"].isNull() || user["password"].isNull())//对用户名和密码进行校验，任意一个没有都视为错误
        {
            DLOG("input username or password!");
            return false;
        }
        //  以用户名和密码作为共同查询条件，查询到表示用户名密码正确，查询不到表示用户名密码错误
    #define LOGIN_USER "select id, score, total_count, win_count from user where username='%s' and password=password('%s');"

        char sql[4096] = {0};
        sprintf(sql, LOGIN_USER, user["username"].asCString(), user["password"].asCString());
        MYSQL_RES* res = NULL;
        {
            unique_lock<mutex> lock(_mutex);

            bool ret = mysql_util::mysql_exec(_mysql, sql);
            if(ret == false)
            {
                DLOG("user login failed!");
                return false;
            }
            // 执行成功，要么有数据，要么没数据，有数据也只有一条
            res = mysql_store_result(_mysql);   // 保存查询结果到本地
            if(res == NULL)
            {
                DLOG("have not login user info!");
                return false;
            }
        }
        int row_num = mysql_num_rows(res);  // 获取结果集中的⾏数
        if(row_num != 1)
        {
            DLOG("the user information queried is not unique!");
            return false;
        }
        MYSQL_ROW row = mysql_fetch_row(res);   //遍历结果集
        user["id"] = (Json::UInt64)stol(row[0]);
        user["score"] = (Json::UInt64)stol(row[1]);
        user["total_count"] = stoi(row[2]);
        user["win_count"] = stoi(row[3]);
        mysql_free_result(res);     // 释放结果集
        return true;
    }
    bool select_by_name(const string& name,Json::Value& user)  //通过用户名获取用户信息
    {
    #define USER_BY_NAME "select id, score, total_count, win_count from user where username='%s';"
        char sql[4096] = {0};
        sprintf(sql, USER_BY_NAME, name.c_str());
        MYSQL_RES* res = NULL;
        {
            unique_lock<mutex> lock(_mutex);

            bool ret = mysql_util::mysql_exec(_mysql, sql);
            if(ret == false)
            {
                DLOG("get user by name failed!");
                return false;
            }
            // 执行成功，要么有数据，要么没数据，有数据也只有一条
            res = mysql_store_result(_mysql);   // 保存查询结果到本地
            if(res == NULL)
            {
                DLOG("have not user info!");
                return false;
            }
        }
        int row_num = mysql_num_rows(res);  // 获取结果集中的⾏数
        if(row_num != 1)
        {
            DLOG("the user information queried is not unique!");
            return false;
        }
        MYSQL_ROW row = mysql_fetch_row(res);   //遍历结果集
        user["id"] = (Json::UInt64)stol(row[0]);
        user["username"] = name;
        user["score"] = (Json::UInt64)stol(row[1]);
        user["total_count"] = stoi(row[2]);
        user["win_count"] = stoi(row[3]);
        mysql_free_result(res);     // 释放结果集
        return true;

    }
    bool select_by_id(uint64_t id, Json::Value& user)  //通过用户id获取用户信息
    {
    #define USER_BY_ID "select username, score, total_count, win_count from user where id=%d;"
        char sql[4096] = {0};
        sprintf(sql, USER_BY_ID, id);
        MYSQL_RES* res = NULL;
        {
            unique_lock<mutex> lock(_mutex);

            bool ret = mysql_util::mysql_exec(_mysql, sql);
            if(ret == false)
            {
                DLOG("get user by id failed!");
                return false;
            }
            // 执行成功，要么有数据，要么没数据，有数据也只有一条
            res = mysql_store_result(_mysql);   // 保存查询结果到本地
            if(res == NULL)
            {
                DLOG("have not user info!");
                return false;
            }
        }
        int row_num = mysql_num_rows(res);  // 获取结果集中的⾏数
        if(row_num != 1)
        {
            DLOG("the user information queried is not unique!");
            return false;
        }
        MYSQL_ROW row = mysql_fetch_row(res);   //遍历结果集
        user["id"] = (Json::UInt64)id;
        user["username"] = row[0];
        user["score"] = (Json::UInt64)stol(row[1]);
        user["total_count"] = stoi(row[2]);
        user["win_count"] = stoi(row[3]);
        mysql_free_result(res);     // 释放结果集
        return true;

    }
    bool win(uint64_t id) //胜利时，天梯分数增加，对局场次增加，胜利场次增加
    {
    #define USER_WIN "update user set score=score+30, total_count=total_count+1, win_count=win_count+1 where id=%d;"
        char sql[4096];
        sprintf(sql, USER_WIN, id);
        bool ret = mysql_util::mysql_exec(_mysql, sql);
        if(ret == false)
        {
            DLOG("updata win user info failed!");
            return false;
        }
        return true;
    }
    bool lose(uint64_t id)  //失败时，天梯分数减少，对局场次增加
    {
    #define USER_LOSE "update user set score=score-30, total_count=total_count+1 where id=%d;"
        char sql[4096];
        sprintf(sql, USER_LOSE, id);
        bool ret = mysql_util::mysql_exec(_mysql, sql);
        if(ret == false)
        {
            DLOG("updata lose user info failed!");
            return false;
        }

        return true;
    }

private:
    MYSQL* _mysql;
    mutex _mutex; 
};
#endif